﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XiaoGang.Soft.XGWMS.COM;
using XiaoGang.Soft.XGWMS.MODEL;
using XiaoGang.Soft.XGWMS.DBUtility;
using System.Collections;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M007_GOODS
    {
        /// <summary>
        /// <p><b>方法名称</b>Init_Combox</p>	
        /// <p><b>概要説明  :</b>初始化商品分类</p>	
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">商品分类信息</returns>
        public ReturnValue Init_Combox()
        {
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M007_GOODS_SQL.Init_Combox_SQL);
        }

        /// <summary>
        /// <p><b>方法名称</b>GetSingleData</p>	
        /// <p><b>概要説明  :</b>取得一条商品信息</p>	
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">单条商品信息</returns>
        public ReturnValue GetSingleData(MOD_M007_GOODS model)
        {
            SqlParameter parameter = new SqlParameter("@GOODS_ID", SqlDbType.Char, 12);
            parameter.Value = model.GOODS_ID;
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M007_GOODS_SQL.Get_Single_Data_SQL, parameter);
        }

        /// <summary>
        /// <p><b>方法名称</b>根据商品条码查询商品</p>	
        /// <p><b>概要説明  :</b>取得一条商品信息</p>	
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="string" name="GOODS_NO">商品条码</param>
        /// <returns type = "ReturnValue">单条商品信息</returns>
        public ReturnValue Get_Single_Data_ByNo(string GOODS_NO)
        {
            SqlParameter parameter = new SqlParameter("@GOODS_NO", SqlDbType.Char, 20);
            parameter.Value = GOODS_NO;
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M007_GOODS_SQL.Get_Single_Data_ByNo_SQL, parameter);
        }

        /// <summary>
        /// <p><b>方法名称</b>CheckName</p>	
        /// <p><b>概要説明  :</b>取得一条商品信息</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">单条商品信息</returns>
        public ReturnValue CheckName(MOD_M007_GOODS model)
        {
            SqlParameter parameter = new SqlParameter("@GOODS_NM1", SqlDbType.VarChar, 256);
            parameter.Value = model.GOODS_NM1;
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M007_GOODS_SQL.Check_Name_SQL, parameter);
        }

        /// <summary>
        /// <p><b>方法名称</b>GoodsDeleteCheck</p>	
        /// <p><b>概要説明  :</b>检测该商品有没有没使用</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/09/16</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="string" name="strGoodsID">商品编码</param>
        /// <returns type = "ReturnValue">商品编码</returns>
        public ReturnValue GoodsDeleteCheck(string strGoodsID)
        {
            SqlParameter parameter = new SqlParameter("@GOODS_ID", SqlDbType.Char, 12);
            parameter.Value = strGoodsID.Trim();
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M007_GOODS_SQL.Goods_Delete_Check_SQL, parameter);
        }

        /// <summary>
        /// <p><b>方法名称</b>SearchProcess</p>	
        /// <p><b>概要説明  :</b>商品信息的检索</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">商品信息表</returns>
        public ReturnValue SearchProcess(MOD_M007_GOODS model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat(@" SELECT M007_GOODS.[CREATE_DATETIME] 
,M007_GOODS.[UPDATE_DATETIME] 
,M007_GOODS.TRANS_STATE
,M007_GOODS.[UPDATER_ID]
,M007_GOODS.[TERMINAL_CD]
,M007_GOODS.[GOODS_ID]
,M007_GOODS.[GOODS_NO]
,M007_GOODS.[GOODS_NM1]
,M007_GOODS.[GOODS_NM2]
,M007_GOODS.[GOODS_GROUP]
,M006_GOODS_GROUP.GOODS_GROUP_NM AS GOODS_GROUP_NM 
,M007_GOODS.[PROVIDER]
,M007_GOODS.[MAX_WARN]
,M007_GOODS.[MIN_WARN]
,M007_GOODS.[MEMO]
,M007_GOODS.[MONEY]
,M007_GOODS.[Discount]*10 as Discount
,M007_GOODS.[Units]
 FROM [M007_GOODS]
 LEFT JOIN [M006_GOODS_GROUP] 
 ON [M006_GOODS_GROUP].GOODS_GROUP_ID = [M007_GOODS].GOODS_GROUP   
 WHERE 1 = 1 ");

            if(1==model.SearchType)
            {
                strSql.AppendFormat(" AND M007_GOODS.TRANS_STATE <> '3'  {0}", (char)13);
            }
            else if (2==model.SearchType)
            {
                strSql.AppendFormat (" AND M007_GOODS.TRANS_STATE = '3'  {0}", (char)13);
            }
            
            if (0 != model.GOODS_ID.Length)
            {
                strSql.AppendFormat(" AND   M007_GOODS.GOODS_ID like '%{0}%'    {1}", model.GOODS_ID, (char)13);
            }
            if (0 != model.GOODS_NO.Length)
            {
                strSql.AppendFormat (" AND (M007_GOODS.GOODS_NO like '%{0}%'  Or  M007_GOODS.Goods_ID IN (Select GoodsID from M013_GoodSNo Where GoodsNo like '%{1}%')) {2}", model.GOODS_NO,model.GOODS_NO, (char)13);
            }
            if (0 != model.GOODS_GROUP.Length)
            {
                strSql.AppendFormat(" AND   M007_GOODS.GOODS_GROUP = '{0}' {1}", model.GOODS_GROUP, (char)13);
            }
            if (0 != model.GOODS_NM1.Length)
            {
                strSql.AppendFormat(" AND  (M007_GOODS.GOODS_NM1 LIKE '%{0}%' Or M007_GOODS.GOODS_NM2 Like '%{1}%' {2} ) ", model.GOODS_NM1, model.GOODS_NM1, (char)13);
            }            
            if (0 != model.MEMO.Length)
            {
                strSql.AppendFormat(" AND   M007_GOODS.MEMO      LIKE '%{0}%' {1}", model.MEMO,         (char)13);
            }
            if (0 != model.PROVIDER.Length)
            {
                strSql.AppendFormat(" AND   M007_GOODS.PROVIDER  LIKE '%{0}%' {1}", model.PROVIDER,     (char)13);
            }
            strSql.AppendFormat(" ORDER BY M006_GOODS_GROUP.OrderNum,M007_GOODS.GOODS_GROUP,M007_GOODS.GOODS_NM1 {0}", (char)13);
            return XGWMS.DBUtility.SqlHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// <p><b>方法名称</b>InsertProcess</p>	
        /// <p><b>概要説明  :</b>商品信息的插入</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue InsertProcess(MOD_M007_GOODS model)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@TRANS_STATE", SqlDbType.Char,1),
                    new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
                    new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                    new SqlParameter("@GOODS_ID", SqlDbType.Char,12),
                    new SqlParameter("@GOODS_NO", SqlDbType.Char,20),
                    new SqlParameter("@GOODS_NM1", SqlDbType.VarChar,256),
                    new SqlParameter("@GOODS_NM2", SqlDbType.VarChar,256),
                    new SqlParameter("@GOODS_GROUP", SqlDbType.Char,12),
                    new SqlParameter("@PROVIDER", SqlDbType.VarChar,256),
                    new SqlParameter("@MAX_WARN", SqlDbType.Int,4),
                    new SqlParameter("@MIN_WARN", SqlDbType.Int,4),
                    new SqlParameter("@MEMO", SqlDbType.VarChar,256),
                    new SqlParameter("@MONEY", SqlDbType.Money),
                    new SqlParameter("@Discount", SqlDbType.Decimal),
                    new SqlParameter("@UNITS", SqlDbType.Int)};
            parameters[0].Value = model.TRANS_STATE;
            parameters[1].Value = model.UPDATER_ID;
            parameters[2].Value = model.TERMINAL_CD;
            parameters[3].Value = model.GOODS_ID;
            parameters[4].Value = model.GOODS_NO;
            parameters[5].Value = model.GOODS_NM1;
            parameters[6].Value = model.GOODS_NM2;
            parameters[7].Value = model.GOODS_GROUP;
            parameters[8].Value = model.PROVIDER;
            if (model.MAX_WARN.HasValue)
            {
                parameters[9].Value = model.MAX_WARN;
            }
            else
            {
                parameters[9].Value = DBNull.Value ;
            }
            if (model.MIN_WARN.HasValue)
            {
                parameters[10].Value = model.MIN_WARN;
            }
            else
            {
                parameters[10].Value = DBNull.Value;
            }
            parameters[11].Value = model.MEMO;
            if (model.SalePrice.HasValue)
            {
                parameters[12].Value = model.SalePrice;
            }
            else
            {
                parameters[12].Value = DBNull.Value;
            }
            parameters[13].Value = model.Discount;
            parameters[14].Value = model.UNITS;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M007_GOODS_SQL.Insert_GOODS_SQL, parameters);
        }

        /// <summary>
        /// <p><b>方法名称</b>AddGoodsByProcedure</p>	
        /// <p><b>概要説明  :</b>添加商品信息</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2011/08/21</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">商品实体</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue AddGoodsByProcedure(MOD_M007_GOODS model)
        {
            //@UpdaterID varchar(12) = ''	/*更新者*/
            //@TerminalID varchar(256) = ''	/*终端*/
            //@Units int = 0				/*数量*/
            //@GoodsID char(12)=''			/*商品编号*/
            //@BillID char(12)=''			/*货单编号*/
            //@StorageID char(12)=''		/*库存编号*/
            //@Resoult char(1) = '0' output	/*执行结果 0:成功 1:失败*/
            //@FiscalYear char(4) = ''		/*年份*/
            //@GOODS_NO char(20) =''		/*商品条码*/
            //@GOODS_NM1 varchar(256) =''	/*商品名称1*/
            //@GOODS_NM2 varchar(256) =''	/*商品名称2*/
            //@GOODS_GROUP char(12) =''		/*商品分类*/
            //@PROVIDER varchar(256)=''		/*提供商*/
            //@MAX_WARN int =null			/*最大库存*/
            //@MIN_WARN int =null			/*最小库存*/
            //@MEMO varchar(256)=''			/*备注*/
            //@MONEY money =null			/*售价*/
            //@Discount decimal(18,1)=null	/*折扣*/
            //@STOREROOM_ID char(12)=null	/*仓库编号*/
            //@BUYING_PRICE money = null	/*进价*/
            SqlParameter[] parameters = {
                    new SqlParameter("@UpdaterID", SqlDbType.VarChar,12),
                    new SqlParameter("@TerminalID", SqlDbType.VarChar,256),
                    new SqlParameter("@Units", SqlDbType.Int),
                    new SqlParameter("@GOODS_NO", SqlDbType.Char,20),
                    new SqlParameter("@GOODS_NM1", SqlDbType.VarChar,256),
                    new SqlParameter("@GOODS_NM2", SqlDbType.VarChar,256),
                    new SqlParameter("@GOODS_GROUP", SqlDbType.Char,12),
                    new SqlParameter("@PROVIDER", SqlDbType.VarChar,256),
                    new SqlParameter("@MAX_WARN", SqlDbType.Int,4),
                    new SqlParameter("@MIN_WARN", SqlDbType.Int,4),
                    new SqlParameter("@MEMO", SqlDbType.VarChar,256),
                    new SqlParameter("@MONEY", SqlDbType.Money),
                    new SqlParameter("@Discount", SqlDbType.Decimal),
                    new SqlParameter("@STOREROOM_ID", SqlDbType.Char,12),
                    new SqlParameter("@BUYING_PRICE", SqlDbType.Money),
                    new SqlParameter("@Resoult", SqlDbType.Char,12)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.UNITS;
            parameters[3].Value = model.GOODS_NO;
            parameters[4].Value = model.GOODS_NM1;
            parameters[5].Value = model.GOODS_NM2;
            parameters[6].Value = model.GOODS_GROUP;
            parameters[7].Value = model.PROVIDER;
            //最大库存警告
            if (model.MAX_WARN.HasValue)
                parameters[8].Value = model.MAX_WARN;
            else
                parameters[8].Value = DBNull.Value;
            //最小库存警告
            if (model.MIN_WARN.HasValue)
                parameters[9].Value = model.MIN_WARN;
            else
                parameters[9].Value = DBNull.Value;
            parameters[10].Value = model.MEMO;
            //售价
            if (model.SalePrice.HasValue)
                parameters[11].Value = model.SalePrice;
            else
                parameters[11].Value = DBNull.Value;
            parameters[12].Value = model.Discount;
            parameters[13].Value = model.StoreRoom_ID;
            //进价
            if (model.BuyPrice.HasValue)
                parameters[14].Value = model.BuyPrice;
            else
                parameters[14].Value = DBNull.Value;
            parameters[15].Direction = ParameterDirection.Output;
            ReturnValue resoult = XGWMS.DBUtility.SqlHelper.ExeProcedure("spAddBillGoods", parameters);
            if (resoult.IsSuccess)
            {
                if (parameters[15].Value.ToString() == "0")
                {
                    resoult = new ReturnValue(false, "E13003");//添加商品失败
                }
                else
                {
                    resoult.ErrorID = parameters[15].Value.ToString();
                }
            }
            return resoult;
        }

        /// <summary>
        /// 商品信息的更新***不更新库存数量***
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ReturnValue UpdateProcess(MOD_M007_GOODS model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
					new SqlParameter("@GOODS_NO", SqlDbType.Char,20),
					new SqlParameter("@GOODS_NM1", SqlDbType.VarChar,256),
					new SqlParameter("@GOODS_NM2", SqlDbType.VarChar,256),
					new SqlParameter("@GOODS_GROUP", SqlDbType.Char,12),
					new SqlParameter("@PROVIDER", SqlDbType.VarChar,256),
					new SqlParameter("@MAX_WARN", SqlDbType.Int,4),
					new SqlParameter("@MIN_WARN", SqlDbType.Int,4),
					new SqlParameter("@MEMO", SqlDbType.VarChar,256),
                    new SqlParameter("@MONEY", SqlDbType.Money),
                    new SqlParameter("@Discount", SqlDbType.Decimal),
                    new SqlParameter("@GOODS_ID", SqlDbType.Char,12)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.GOODS_NO;
            parameters[3].Value = model.GOODS_NM1;
            parameters[4].Value = model.GOODS_NM2;
            parameters[5].Value = model.GOODS_GROUP;
            parameters[6].Value = model.PROVIDER;
            //最大库存警戒
            if (model.MAX_WARN.HasValue)
                parameters[7].Value = model.MAX_WARN;
            else
                parameters[7].Value = System.DBNull.Value;
            //最小库存警戒
            if (model.MIN_WARN.HasValue)
                parameters[8].Value = model.MIN_WARN;
            else
                parameters[8].Value = System.DBNull.Value;
            parameters[9].Value = model.MEMO;//备注
            //售价
            if (model.SalePrice.HasValue)
                parameters[10].Value = model.SalePrice;
            else
                parameters[10].Value = System.DBNull.Value;
            parameters[11].Value = model.Discount;
            parameters[12].Value = model.GOODS_ID;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M007_GOODS_SQL.Update_GOODS_GROUP_SQL, parameters);
        }

        /// <summary>
        /// <p><b>方法名称</b>LogicDeleteProcess</p>	
        /// <p><b>概要説明  :</b>商品信息的逻辑删除</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">更新信息和条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue LogicDeleteProcess(MOD_M007_GOODS model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                    new SqlParameter("@GOODS_ID", SqlDbType.Char,12)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.GOODS_ID;

            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M007_GOODS_SQL.Logic_Delete_GOODS_GROUP_SQL, parameters);
        }

        /// <summary>
        /// 物理、彻底删除商品
        /// </summary>
        /// <param name="model"></param>
        public ReturnValue PhysicalDeleteProcess(string GoodsID)
        {
            SqlParameter parameter = new SqlParameter("@GOODS_ID", SqlDbType.VarChar, 12);
            parameter.Value = GoodsID;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M007_GOODS_SQL.Physical_Delete_GOODS_GROUP_SQL, parameter);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ReturnValue Update_Goods_Units(MOD_M007_GOODS model)
        {
            SqlParameter[] parameters = {new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                    new SqlParameter("@UNITS", SqlDbType.Int),
                    new SqlParameter("@GOODS_ID", SqlDbType.Char,12)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.UNITS;
            parameters[3].Value = model.GOODS_ID;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M007_GOODS_SQL.Update_Goods_Units_SQL, parameters);
        }

        public ReturnValue Add_Goods_Units(MOD_M007_GOODS model)
        {
            SqlParameter[] parameters = {new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                    new SqlParameter("@UNITS", SqlDbType.Int),
                    new SqlParameter("@GOODS_ID", SqlDbType.Char,12)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.UNITS;
            parameters[3].Value = model.GOODS_ID;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M007_GOODS_SQL.Add_Goods_Units_SQL, parameters);
        }

        /// <summary>
        /// 取得非本ID但相同名称的商品
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public ReturnValue CheckGoodsNameWithoutID(string GoodsID, string GoodsName)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(GoodsID))
            {
                strWhere += String.Format(" and GOODS_ID <> '{0}'", GoodsID);
            }

            if (!string.IsNullOrEmpty(GoodsName))
            {
                strWhere += String.Format(" and GOODS_NM1 = '{0}'", GoodsName);
            }
            return SqlHelper.Query(DAL_M007_GOODS_SQL.GetGoodsID + strWhere);
        }

        /// <summary>
        /// 根据商品编号检查商品是否存在
        /// </summary>
        /// <param name="GoodsID"></param>
        /// <returns></returns>
        public ReturnValue CheckGoodsExist(string GoodsID)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(GoodsID))
            {
                strWhere += String.Format(" and GOODS_ID = '{0}'", GoodsID);
            }
            return SqlHelper.Query(DAL_M007_GOODS_SQL.GetGoodsID + strWhere);
        }
    }
}
